package com.such.kit.database;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.collections4.multimap.ArrayListValuedHashMap;
import com.such.kit.Logger;
import com.such.kit.database.bean.DDLColumn;
import com.such.kit.database.bean.DDLForeignKey;
import com.such.kit.database.bean.DDLIndex;
import com.such.kit.database.bean.DDLPrimaryKey;
import com.such.kit.database.bean.DDLSchema;
import com.such.kit.database.bean.DDLTable;
import com.such.kit.database.bean.DDLTableKey;
import com.such.kit.database.bean.DatabaseColumn;
import com.such.kit.database.bean.DatabaseForeignKey;
import com.such.kit.database.bean.DatabaseIndex;
import com.such.kit.database.bean.DatabasePrimaryKey;
import com.such.kit.database.bean.DatabaseSchema;
import com.such.kit.database.bean.DatabaseTable;
import com.such.kit.database.dialect.DialectCommons;
import com.such.kit.string.StringCommons;
import com.such.kit.validate.ValidateCommons;

/**
 * @author SUCH
 * <pre>
 * 数据库元信息工具类
 * </pre>
 */
public class MetaDataCommons {

	public static final String DB_TYPE_ORACLE = "oracle";
	public static final String DB_TYPE_DB2 = "db2";
	public static final String DB_TYPE_MYSQL = "mysql";
	public static final String DB_TYPE_SQLSERVER = "sqlserver";
	public static final String DB_TYPE_SYBASE = "sybase";
	public static final String DB_TYPE_DM = "dm";

	private String driver;
	private String url;
	private String username;
	private String password;
	private DataSource dataSource;
	private DialectCommons dialectCommons;
	private String dbType;

	/**
	 * <pre>
	 * 基于 JDBC 4 要素创建 MetaDataCommons
	 * </pre>
	 * @param driver 驱动
	 * @param url URL
	 * @param username 用户名
	 * @param password 密码
	 */
	public MetaDataCommons(String driver, String url, String username, String password) {
		this.driver = driver;
		this.url = url;
		this.username = username;
		this.password = password;
		try {
			this.dialectCommons = DialectCommons.getInstance(getDbType());
		} catch (Exception e) {
			Logger.error(getClass(), "初始化 DialectCommons 异常", e);
		}
	}

	/**
	 * <pre>
	 * 基于 DataSource 创建 MetaDataCommons
	 * 使用 DataSource （Oracle、Mysql）无法获取表备注
	 * </pre>
	 * @param dataSource 数据源
	 */
	public MetaDataCommons(DataSource dataSource) {
		this.dataSource = dataSource;
		try {
			this.dialectCommons = DialectCommons.getInstance(getDbType());
		} catch (Exception e) {
			Logger.error(getClass(), "初始化 DialectCommons 异常", e);
		}
	}

	private Connection getConnection() 
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		if (ValidateCommons.isNotEmpty(this.dataSource)) {
			conn = JdbcCommons.open(this.dataSource);
		} else if (ValidateCommons.isNotEmpty(this.driver) && ValidateCommons.isNotEmpty(this.url) && ValidateCommons.isNotEmpty(this.username) && ValidateCommons.isNotEmpty(this.password)) {
			conn = JdbcCommons.open(this.driver, this.url, this.username, this.password);
		}
		return conn;
	}

	/**
	 * <pre>
	 * 获取 SQL 语句方言处理工具
	 * </pre>
	 * @return SQL 语句方言处理工具
	 */
	public DialectCommons getDialectCommons() {
		return this.dialectCommons;
	}

	/**
	 * <pre>
	 * 获取数据库类型
	 * </pre>
	 * @return 数据库类型 [参考 DB_TYPE_xxx]
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public String getDbType() 
			throws SQLException, ClassNotFoundException {
		if (ValidateCommons.isEmpty(this.dbType)) {
			this.dbType = getDbType(getConnection(), true);
		}
		return this.dbType;
	}

	/**
	 * <pre>
	 * 通过 JDBC 4 要素创建 {@link Connection} 获取数据库类型
	 * </pre>
	 * @param driver 驱动
	 * @param url URL
	 * @param username 用户名
	 * @param password 密码
	 * @return 数据库类型 [参考 DB_TYPE_xxx]
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static String getDbType(String driver, String url, String username, String password) 
			throws SQLException, ClassNotFoundException {
		return getDbType(JdbcCommons.open(driver, url, username, password), true);
	}

	/**
	 * <pre>
	 * 从数据源获取 {@link Connection} 并获取数据库类型
	 * </pre>
	 * @param dataSource 数据源
	 * @return 数据库类型 [参考 DB_TYPE_xxx]
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static String getDbType(DataSource dataSource) 
			throws SQLException, ClassNotFoundException {
		return getDbType(JdbcCommons.open(dataSource), true);
	}

	/**
	 * <p>方法名：getDbType</p>
	 * <p>描述：获取数据库类型</p>
	 * @return 数据库类型，参照 DB_TYPE_xxx
	 * @param conn 数据库连接
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	/**
	 * <pre>
	 * 从 {@link Connection} 数据库元信息并获取数据库类型
	 * </pre>
	 * @param conn 数据库连接
	 * @param closeable 是否关闭连接
	 * @return 数据库类型 [参考 DB_TYPE_xxx]
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static String getDbType(Connection conn, boolean closeable) 
			throws SQLException, ClassNotFoundException {
		String dbType = null;
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			String productName = databaseMetaData.getDatabaseProductName();
			productName = productName.toLowerCase();
			if (productName.indexOf("oracle") > -1) {
				dbType = DB_TYPE_ORACLE;
			} else if (productName.indexOf("db2") > -1) {
				dbType = DB_TYPE_DB2;
			} else if (productName.indexOf("mysql") > -1) {
				dbType = DB_TYPE_MYSQL;
			} else if (productName.indexOf("microsoft sql server") > -1) {
				dbType = DB_TYPE_SQLSERVER;
			} else if (productName.indexOf("adaptive server") > -1) {
				dbType = DB_TYPE_SYBASE;
			} else if (productName.indexOf("dm dbms") > -1) {
				dbType = DB_TYPE_DM;
			}
		} finally {
			if (closeable) {
				JdbcCommons.close(conn, null, null);
			}
		}
		return dbType;
	}

	/**
	 * <pre>
	 * 获取表模式列表
	 * </pre>
	 * @param schemaPattern 表模式，["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @return 表模式列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabaseSchema> getSchemas(String schemaPattern) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseSchema> schemas = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			schemas = getSchemas(databaseMetaData, conn.getCatalog(), schemaPattern);
		} finally {
			JdbcCommons.close(conn);
		}
		return schemas;
	}

	/**
	 * <pre>
	 * 获取表模式列表并转化为 DDLSchema
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @return 表模式列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DDLSchema> getDDLSchemas(String schemaPattern) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseSchema> schemas = getSchemas(schemaPattern);
		List<DDLSchema> ddlSchemas = new ArrayList<DDLSchema>();
		if (ValidateCommons.isNotEmpty(schemas)) {
			for (DatabaseSchema schema : schemas) {
				ddlSchemas.add(new DDLSchema(schema));
			}
		}
		return ddlSchemas;
	}

	private List<DatabaseSchema> getSchemas(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabaseSchema> schemas = new ArrayList<DatabaseSchema>();
		ResultSet rs = null;
		try {
			rs = databaseMetaData.getSchemas(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern));
			while (rs.next()) {
				DatabaseSchema schema = (DatabaseSchema) JdbcCommons.buildObject(rs, DatabaseSchema.class);
				schemas.add(schema);
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return schemas;
	}

	/**
	 * <pre>
	 * 获取数据库表列表
	 * 
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableNamePattern 表名，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param types 表类型 [参考 {@link DatabaseTable}.TYPE_xxx]
	 * @param infos 表扩展信息类型 [参考 {@link DatabaseTable}.INFO_xxx]
	 * @return 表列表
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabaseTable> getTables(String schemaPattern, String tableNamePattern, String[] types, String[] infos) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			ClassNotFoundException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseTable> tables = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			tables = getTables(databaseMetaData, conn.getCatalog(), schemaPattern, tableNamePattern, types, infos);
		} finally {
			JdbcCommons.close(conn);
		}
		return tables;
	}

	/**
	 * <pre>
	 * 获取数据库表列表并转化为 DDLTable
	 * 
	 * 参数细节
	 * schemaPattern [oracle > username]
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableNamePattern 表名，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param types 表类型 [参考 {@link DatabaseTable}.TYPE_xxx]
	 * @param infos 表扩展信息类型 [参考 {@link DatabaseTable}.INFO_xxx]
	 * @return 表列表
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DDLTable> getDDLTables(String schemaPattern, String tableNamePattern, String[] types, String[] infos) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			ClassNotFoundException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseTable> tables = getTables(schemaPattern, tableNamePattern, types, infos);
		List<DDLTable> ddlTables = new ArrayList<DDLTable>();
		if (ValidateCommons.isNotEmpty(tables)) {
			for (DatabaseTable table : tables) {
				ddlTables.add(new DDLTable(table));
			}
		}
		return ddlTables;
	}

	private List<DatabaseTable> getTables(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern, String tableNamePattern, String[] types, String[] infos) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabaseTable> tables = new ArrayList<DatabaseTable>();
		ResultSet rs = null;
		try {
			rs = databaseMetaData.getTables(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern), StringCommons.toUpperCase(tableNamePattern), types);
			while (rs.next()) {
				DatabaseTable table = (DatabaseTable) JdbcCommons.buildObject(rs, DatabaseTable.class);
				String tableName = table.getName();

				if (ValidateCommons.isNotEmpty(tableName)) {
					if (ValidateCommons.isNotEmpty(infos)) {
						for (String info : infos) {
							// 主键、外键、索引 不支持通配符，只能指定表名
							if (DatabaseTable.INFO_PRIMARY_KEY.equals(info)) {
								table.setPrimaryKeys(getPrimaryKeys(databaseMetaData, catalog, schemaPattern, tableName));
							} else if (DatabaseTable.INFO_FOREIGN_KEY.equals(info)) {
								table.setForeignKeys(getForeignKeys(databaseMetaData, catalog, schemaPattern, tableName));
							} else if (DatabaseTable.INFO_INDEX.equals(info)) {
								table.setIndexs(getIndexs(databaseMetaData, catalog, schemaPattern, tableName, false, true));
							}
						}
					}
					tables.add(table);
				}
			}
			if (ValidateCommons.isNotEmpty(infos)) {
				ArrayListValuedHashMap<DDLTableKey, DatabaseColumn> columnsMap = new ArrayListValuedHashMap<DDLTableKey, DatabaseColumn>();
				for (String info : infos) {
					if (DatabaseTable.INFO_COLUMN.equals(info)) {
						List<DatabaseColumn> columns = getColumns(databaseMetaData, catalog, schemaPattern, tableNamePattern, null);
						if (ValidateCommons.isNotEmpty(columns)) {
							for (DatabaseColumn column : columns) {
								columnsMap.put(new DDLTableKey(column.getSchema(), column.getTableName()), column);
							}
						}
					}
				}
				for (DatabaseTable table : tables) {
					DDLTableKey tableKey = new DDLTableKey(table.getSchema(), table.getName());
					List<DatabaseColumn> columns = columnsMap.get(tableKey);
					if (ValidateCommons.isNotEmpty(columns)) {
						table.setColumns(columns);
					}
				}
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return tables;
	}

	/**
	 * <pre>
	 * 获取列列表
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableNamePattern 表名，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param columnNamePattern 列名 ["" 获取列名为 "" 的数据 | null 获取所有列]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @return 列列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabaseColumn> getColumns(String schemaPattern, String tableNamePattern, String columnNamePattern) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseColumn> columns = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			columns = getColumns(databaseMetaData, conn.getCatalog(), schemaPattern, tableNamePattern, columnNamePattern);
		} finally {
			JdbcCommons.close(conn);
		}
		return columns;
	}
	
	private List<DatabaseColumn> getColumns(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabaseColumn> columns = new ArrayList<DatabaseColumn>();
		ResultSet rs = null;
		try {
			rs = databaseMetaData.getColumns(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern), StringCommons.toUpperCase(tableNamePattern), 
					StringCommons.toUpperCase(columnNamePattern));
			while (rs.next()) {
				DatabaseColumn column = (DatabaseColumn) JdbcCommons.buildObject(rs, DatabaseColumn.class);
				columns.add(column);
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return columns;
	}

	/**
	 * <pre>
	 * 获取主键列表
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableName 表名
	 * @return 主键列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabasePrimaryKey> getPrimaryKeys(String schemaPattern, String tableName) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabasePrimaryKey> primaryKeys = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			primaryKeys = getPrimaryKeys(databaseMetaData, conn.getCatalog(), schemaPattern, tableName);
		} finally {
			JdbcCommons.close(conn);
		}
		return primaryKeys;
	}

	private List<DatabasePrimaryKey> getPrimaryKeys(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern, String tableName) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabasePrimaryKey> primaryKeys = new ArrayList<DatabasePrimaryKey>();
		ResultSet rs = null;
		try {
			rs = databaseMetaData.getPrimaryKeys(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern), StringCommons.toUpperCase(tableName));
			while (rs.next()) {
				DatabasePrimaryKey primaryKey = (DatabasePrimaryKey) JdbcCommons.buildObject(rs, DatabasePrimaryKey.class);
				primaryKeys.add(primaryKey);
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return primaryKeys;
	}

	/**
	 * <pre>
	 * 获取外键列表
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableName 表名
	 * @return 外键列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabaseForeignKey> getForeignKeys(String schemaPattern, String tableName) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseForeignKey> foreignKeys = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			foreignKeys = getForeignKeys(databaseMetaData, conn.getCatalog(), schemaPattern, tableName);
		} finally {
			JdbcCommons.close(conn);
		}
		return foreignKeys;
	}

	private List<DatabaseForeignKey> getForeignKeys(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern, String tableName) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabaseForeignKey> exportedKeys = new ArrayList<DatabaseForeignKey>();
		ResultSet rs = null;
		try {
			/*
			 * getImportedKeys 与 getExportedKeys 的区别
			 * 假设有 A > B（A 中的字段值指向 B 中的字段），一般我们称 A 有外键指向 B
			 * getImportedKeys tableName 应该是 A
			 * getExportedKeys tableName 应该是 B
			 * 也就是说二个方法关于外键的视角是不同的，按照一般的使用习惯，应使用 getImportedKeys 获取外键
			 */
			rs = databaseMetaData.getImportedKeys(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern), StringCommons.toUpperCase(tableName));
			while (rs.next()) {
				DatabaseForeignKey exportedKey = (DatabaseForeignKey) JdbcCommons.buildObject(rs, DatabaseForeignKey.class);
				exportedKeys.add(exportedKey);
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return exportedKeys;
	}

	/**
	 * <pre>
	 * 获取索引列表
	 * </pre>
	 * @param schemaPattern 表模式 ["" 获取没有模式的数据 | null 获取所有模式的数据]，可以使用单字符通配符 [_] 或多字符通配符 [%]，当参数中存在 _ % 则使用 / 进行转义
	 * @param tableName 表名
	 * @param unique [true 返回唯一索引 | false 返回所有索引]
	 * @param approximate [true 返回接近的或以外的值 | false 返回精确值]
	 * @return 索引列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws SecurityException
	 * @throws IllegalArgumentException
	 * @throws NoSuchMethodException
	 * @throws InvocationTargetException
	 */
	public List<DatabaseIndex> getIndexs(String schemaPattern, String tableName, boolean unique, boolean approximate) 
			throws SQLException, ClassNotFoundException, InstantiationException, 
			IllegalAccessException, SecurityException, IllegalArgumentException, 
			NoSuchMethodException, InvocationTargetException {
		List<DatabaseIndex> indexs = null;
		Connection conn = getConnection();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			indexs = getIndexs(databaseMetaData, conn.getCatalog(), schemaPattern, tableName, unique, approximate);
		} finally {
			JdbcCommons.close(conn);
		}
		return indexs;
	}

	private List<DatabaseIndex> getIndexs(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern, String tableName, boolean unique, boolean approximate) 
			throws SQLException, InstantiationException, IllegalAccessException, 
			SecurityException, IllegalArgumentException, NoSuchMethodException, 
			InvocationTargetException {
		List<DatabaseIndex> indexs = new ArrayList<DatabaseIndex>();
		ResultSet rs = null;
		try {
			rs = databaseMetaData.getIndexInfo(StringCommons.toUpperCase(catalog), StringCommons.toUpperCase(schemaPattern), StringCommons.toUpperCase(tableName), 
					unique, approximate);
			while (rs.next()) {
				DatabaseIndex index = (DatabaseIndex) JdbcCommons.buildObject(rs, DatabaseIndex.class);
				if (ValidateCommons.isNotEmpty(index.getIndexName()) && ValidateCommons.isNotEmpty(index.getColumnName())) {
					indexs.add(index);
				}
			}
		} finally {
			JdbcCommons.close(rs);
		}
		return indexs;
	}

	/**
	 * <pre>
	 * 创建表
	 * </pre>
	 * @param table DatabaseTable
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> createTable(DatabaseTable table, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return createTable(new DDLTable(table), execute);
	}

	/**
	 * <pre>
	 * 创建表
	 * </pre>
	 * @param table DDLTable
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> createTable(DDLTable table, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildCreateTableDDL(table);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 删除表
	 * </pre>
	 * @param table DatabaseTable
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropTable(DatabaseTable table, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return dropTable(new DDLTable(table), execute);
	}

	/**
	 * <pre>
	 * 删除表
	 * </pre>
	 * @param table DDLTable
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropTable(DDLTable table, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildDropTableDDL(table);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 重命名表
	 * </pre>
	 * @param table DatabaseTable
	 * @param changedTableName 变更的表名
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameTable(DatabaseTable table, String changedTableName, boolean execute) 
			throws SQLException, ClassNotFoundException {
		DDLTable ddlTable = new DDLTable(table);
		ddlTable.setChangedTableName(changedTableName);
		return renameTable(ddlTable, execute);
	}

	/**
	 * <pre>
	 * 重命名表
	 * </pre>
	 * @param table DDLTable
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameTable(DDLTable table, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildRenameTableDDL(table);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 新增列
	 * </pre>
	 * @param column DatabaseColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addColumn(DatabaseColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return addColumn(new DDLColumn(column), execute);
	}

	/**
	 * <pre>
	 * 新增列
	 * </pre>
	 * @param column DDLColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addColumn(DDLColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildAddColumnDDL(column);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 修改列
	 * </pre>
	 * @param column DatabaseColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> modifyColumn(DatabaseColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return modifyColumn(new DDLColumn(column), execute);
	}

	/**
	 * <pre>
	 * 修改列
	 * </pre>
	 * @param column DDLColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> modifyColumn(DDLColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildModifyColumnDDL(column);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 删除列
	 * </pre>
	 * @param column DatabaseColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropColumn(DatabaseColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return dropColumn(new DDLColumn(column), execute);
	}

	/**
	 * <pre>
	 * 删除列
	 * </pre>
	 * @param column DDLColumn
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropColumn(DDLColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildDropColumnDDL(column);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 重命名列
	 * </pre>
	 * @param column DatabaseColumn
	 * @param changedColumnName 变更的列名
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameColumn(DatabaseColumn column, String changedColumnName, boolean execute) 
			throws SQLException, ClassNotFoundException {
		DDLColumn ddlColumn = new DDLColumn(column);
		ddlColumn.setChangedColumnName(changedColumnName);
		return renameColumn(ddlColumn, execute);
	}

	/**
	 * <pre>
	 * 重命名列
	 * </pre>
	 * @param column DDLColumn
	 * @param changedColumnName 变更的列名
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameColumn(DDLColumn column, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildRenameColumnDDL(column);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 新增主键
	 * 
	 * primaryKeys 需要属于同一个主键，存在多个只构建列表第一个相关主键
	 * </pre>
	 * @param primaryKeys DatabasePrimaryKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addPrimaryKey(List<DatabasePrimaryKey> primaryKeys, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return addPrimaryKey(new DDLPrimaryKey(primaryKeys), execute);
	}

	/**
	 * <pre>
	 * 新增主键
	 * </pre>
	 * @param primaryKey DDLPrimaryKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addPrimaryKey(DDLPrimaryKey primaryKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildAddPrimaryKeyDDL(primaryKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 删除主键
	 * 
	 * primaryKeys 需要属于同一个主键，存在多个只构建列表第一个相关主键
	 * </pre>
	 * @param primaryKeys DatabasePrimaryKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropPrimaryKey(List<DatabasePrimaryKey> primaryKeys, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return dropPrimaryKey(new DDLPrimaryKey(primaryKeys), execute);
	}

	/**
	 * <pre>
	 * 删除主键
	 * </pre>
	 * @param primaryKey DDLPrimaryKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropPrimaryKey(DDLPrimaryKey primaryKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildDropPrimaryKeyDDL(primaryKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 重命名主键
	 * 
	 * primaryKeys 需要属于同一个主键，存在多个只构建列表第一个相关主键
	 * </pre>
	 * @param primaryKeys DatabasePrimaryKey
	 * @param changedPkName 变更的主键名
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renamePrimaryKey(List<DatabasePrimaryKey> primaryKeys, String changedPkName, boolean execute) 
			throws SQLException, ClassNotFoundException {
		DDLPrimaryKey primaryKey = new DDLPrimaryKey(primaryKeys);
		primaryKey.setChangedPkName(changedPkName);
		return renamePrimaryKey(primaryKey, execute);
	}

	/**
	 * <pre>
	 * 重命名主键
	 * </pre>
	 * @param primaryKey DDLPrimaryKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renamePrimaryKey(DDLPrimaryKey primaryKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildRenamePrimaryKeyDDL(primaryKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 新增外键
	 * 
	 * foreignKeys 需要属于同一个外键，存在多个只构建列表第一个相关外键
	 * </pre>
	 * @param foreignKeys DatabaseForeignKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addForeignKey(List<DatabaseForeignKey> foreignKeys, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return addForeignKey(new DDLForeignKey(foreignKeys), execute);
	}

	/**
	 * <pre>
	 * 新增外键
	 * </pre>
	 * @param foreignKey DDLForeignKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addForeignKey(DDLForeignKey foreignKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildAddForeignKeyDDL(foreignKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 删除外键
	 * 
	 * foreignKeys 需要属于同一个外键，存在多个只构建列表第一个相关外键
	 * </pre>
	 * @param foreignKeys DatabaseForeignKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropForeignKey(List<DatabaseForeignKey> foreignKeys, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return dropForeignKey(new DDLForeignKey(foreignKeys), execute);
	}

	/**
	 * <pre>
	 * 删除外键
	 * </pre>
	 * @param foreignKey DDLForeignKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropForeignKey(DDLForeignKey foreignKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildDropForeignKeyDDL(foreignKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 重命名外键
	 * 
	 * foreignKeys 需要属于同一个外键，存在多个只构建列表第一个相关外键
	 * </pre>
	 * @param foreignKeys DatabaseForeignKey
	 * @param changedFkName 变更的外键名称
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameForeignKey(List<DatabaseForeignKey> foreignKeys, String changedFkName, boolean execute) 
			throws SQLException, ClassNotFoundException {
		DDLForeignKey foreignKey = new DDLForeignKey(foreignKeys);
		foreignKey.setChangedFkName(changedFkName);
		return renameForeignKey(foreignKey, execute);
	}

	/**
	 * <pre>
	 * 重命名外键
	 * </pre>
	 * @param foreignKey DDLForeignKey
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameForeignKey(DDLForeignKey foreignKey, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildRenameForeignKeyDDL(foreignKey);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 新增索引
	 * 
	 * indexs 需要属于同一个索引，存在多个只构建列表第一个相关索引
	 * </pre>
	 * @param indexs DatabaseIndex
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addIndex(List<DatabaseIndex> indexs, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return addIndex(new DDLIndex(indexs), execute);
	}

	/**
	 * <pre>
	 * 新增索引
	 * </pre>
	 * @param index DDLIndex
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> addIndex(DDLIndex index, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildAddIndexDDL(index);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 删除索引
	 * 
	 * indexs 需要属于同一个索引，存在多个只构建列表第一个相关索引
	 * </pre>
	 * @param indexs DatabaseIndex
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropIndex(List<DatabaseIndex> indexs, boolean execute) 
			throws SQLException, ClassNotFoundException {
		return dropIndex(new DDLIndex(indexs), execute);
	}

	/**
	 * <pre>
	 * 删除索引
	 * </pre>
	 * @param index DDLIndex
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> dropIndex(DDLIndex index, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildDropIndexDDL(index);
		executeBatch(ddls, execute);
		return ddls;
	}

	/**
	 * <pre>
	 * 重命名索引
	 * 
	 * indexs 需要属于同一个索引，存在多个只构建列表第一个相关索引
	 * </pre>
	 * @param indexs DatabaseIndex
	 * @param changedIndexName 变更的索引名称
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameIndex(List<DatabaseIndex> indexs, String changedIndexName, boolean execute) 
			throws SQLException, ClassNotFoundException {
		DDLIndex index = new DDLIndex(indexs);
		index.setChangedIndexName(changedIndexName);
		return renameIndex(index, execute);
	}

	/**
	 * <pre>
	 * 重命名索引
	 * </pre>
	 * @param index DDLIndex
	 * @param execute 是否执行 DDL
	 * @return DLL 语句列表
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<String> renameIndex(DDLIndex index, boolean execute) 
			throws SQLException, ClassNotFoundException {
		List<String> ddls = this.dialectCommons.DDL.buildRenameIndexDDL(index);
		executeBatch(ddls, execute);
		return ddls;
	}

	private void executeBatch(List<String> sqls, boolean execute) 
			throws SQLException, ClassNotFoundException {
		if (execute && ValidateCommons.isNotEmpty(sqls)) {
			Connection conn = getConnection();
			Statement st = conn.createStatement();
			try {
				for (String sql : sqls) {
					try {
						st.execute(sql);
					} catch (Exception e) {
						Logger.error(getClass(), StringCommons.merger("SQL [", sql, "] 执行异常"), e);
					}
				}
			} finally {
				JdbcCommons.close(conn, st, null);
			}
		}
	}

}
